//
// StoreNuke.Net -  http://www.storenuke.net
// Copyright (c) 2005
// by Robert J Collins ( info@storenuke.net ) of WillowTree Software, Inc. ( http://www.willowtreesoftware.com )
//
// Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated 
// documentation files (the "Software"), to deal in the Software without restriction, including without limitation 
// the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and 
// to permit persons to whom the Software is furnished to do so, subject to the following conditions:
//
// The above copyright notice and this permission notice shall be included in all copies or substantial portions 
// of the Software.
//
// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED 
// TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL 
// THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF 
// CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER 
// DEALINGS IN THE SOFTWARE.
//

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace StoreNuke.StarterKit.Commerce {

    //*******************************************************
    //
    // ReviewsDB Class
    //
    // Business/Data Logic Class that encapsulates all data
    // logic necessary to list/access/add reviews from
    // the Commerce Starter Kit Reviews database.
    //
    //*******************************************************

    public class ReviewsDB {

        //*******************************************************
        //
        // ReviewsDB.GetReviews() Method <a name="GetReviews"></a>
        //
        // The GetReviews method returns a struct containing
        // a forward-only, read-only DataReader.  This displays a list of all
        // user-submitted reviews for a specified product.
        // The SQLDataReaderResult struct also returns the SQL connection,
        // which must be explicitly closed after the data from the DataReader
        // is bound into the controls.
        //
        // Other relevant sources:
        //     + <a href="ReviewsList.htm" style="color:green">ReviewsList Stored Procedure</a>
        //
        //*******************************************************

        public SqlDataReader GetReviews(int productID) {

            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
            SqlCommand myCommand = new SqlCommand("StoreNuke_ReviewsList", myConnection);

            // Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure;

            // Add Parameters to SPROC
            SqlParameter parameterProductID = new SqlParameter("@ProductID", SqlDbType.Int, 4);
            parameterProductID.Value = productID;
            myCommand.Parameters.Add(parameterProductID);

            // Execute the command
            myConnection.Open();
            SqlDataReader result = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

            // Return the datareader result
            return result;
        }

		public SqlDataReader GetAllReviews(bool authorized) 
		{
			// Create Instance of Connection and Command Object
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("StoreNuke_GetAllReviewsList", myConnection);

			// Mark the Command as a SPROC
			myCommand.CommandType = CommandType.StoredProcedure;

			// Add Parameters to SPROC
			SqlParameter parameterAuthorized = new SqlParameter("@Authorized", SqlDbType.Bit);
			parameterAuthorized.Value = authorized;
			myCommand.Parameters.Add(parameterAuthorized);

			// Execute the command
			myConnection.Open();
			SqlDataReader result = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

			// Return the datareader result
			return result;
		}

        //*******************************************************
        //
        // ReviewsDB.AddReview() Method <a name="AddReview"></a>
        //
        // The AddReview method adds a new review into the
        // Commerce Starter Kit Reviews database.
        //
        // Other relevant sources:
        //     + <a href="ReviewsAdd.htm" style="color:green">ReviewsAdd Stored Procedure</a>
        //
        //*******************************************************

        public void AddReview(int productID, string customerName, string customerEmail, int rating, string comments) {

            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
            SqlCommand myCommand = new SqlCommand("StoreNuke_ReviewsAdd", myConnection);

            // Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure;

            // Add Parameters to SPROC
            SqlParameter parameterProductID = new SqlParameter("@ProductID", SqlDbType.Int, 4);
            parameterProductID.Value = productID;
            myCommand.Parameters.Add(parameterProductID);

            SqlParameter parameterCustomerName = new SqlParameter("@CustomerName", SqlDbType.NVarChar, 50);
            parameterCustomerName.Value = customerName;
            myCommand.Parameters.Add(parameterCustomerName);

            SqlParameter parameterEmail = new SqlParameter("@CustomerEmail", SqlDbType.NVarChar, 50);
            parameterEmail.Value = customerEmail;
            myCommand.Parameters.Add(parameterEmail);

            SqlParameter parameterRating = new SqlParameter("@Rating", SqlDbType.Int, 4);
            parameterRating.Value = rating;
            myCommand.Parameters.Add(parameterRating);

            SqlParameter parameterComments = new SqlParameter("@Comments", SqlDbType.NVarChar, 3850);
            parameterComments.Value = comments;
            myCommand.Parameters.Add(parameterComments);

			SqlParameter parameterAuthorized = new SqlParameter("@Authorized", SqlDbType.Bit);
			parameterAuthorized.Value = Convert.ToBoolean(ConfigurationSettings.AppSettings["AuthorizeReviews"]);
			myCommand.Parameters.Add(parameterAuthorized);

            SqlParameter parameterReviewID = new SqlParameter("@ReviewID", SqlDbType.Int, 4);
            parameterReviewID.Direction = ParameterDirection.Output;
			myCommand.Parameters.Add(parameterReviewID);

            // Open the connection and execute the Command
            myConnection.Open();
            myCommand.ExecuteNonQuery();
            myConnection.Close();
        }

		public void UpdateReview(int reviewID, string comments, bool authorized) 
		{
			// Create Instance of Connection and Command Object
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("StoreNuke_UpdateReview", myConnection);

			// Mark the Command as a SPROC
			myCommand.CommandType = CommandType.StoredProcedure;

			// Add Parameters to SPROC
			SqlParameter parameterReviewID = new SqlParameter("@ReviewID", SqlDbType.Int, 4);
			parameterReviewID.Value = reviewID;
			myCommand.Parameters.Add(parameterReviewID);

			SqlParameter parameterComments = new SqlParameter("@Comments", SqlDbType.NVarChar, 3850);
			parameterComments.Value = comments;
			myCommand.Parameters.Add(parameterComments);

			SqlParameter parameterAuthorized = new SqlParameter("@Authorized", SqlDbType.Bit);
			parameterAuthorized.Value = authorized;
			myCommand.Parameters.Add(parameterAuthorized);

			// Open the connection and execute the Command
			myConnection.Open();
			myCommand.ExecuteNonQuery();
			myConnection.Close();
		}
        
		public void DeleteReview(int reviewID) 
		{
			// Create Instance of Connection and Command Object
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("StoreNuke_DeleteReview", myConnection);

			// Mark the Command as a SPROC
			myCommand.CommandType = CommandType.StoredProcedure;

			// Add Parameters to SPROC
			SqlParameter parameterReviewID = new SqlParameter("@ReviewID", SqlDbType.Int, 4);
			parameterReviewID.Value = reviewID;
			myCommand.Parameters.Add(parameterReviewID);

			// Open the connection and execute the Command
			myConnection.Open();
			myCommand.ExecuteNonQuery();
			myConnection.Close();
		}

		public SqlDataReader GetSingleReview(int reviewID) 
		{
			// Create Instance of Connection and Command Object
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("StoreNuke_GetSingleReview", myConnection);

			// Mark the Command as a SPROC
			myCommand.CommandType = CommandType.StoredProcedure;

			// Add Parameters to SPROC
			SqlParameter parameterReviewID = new SqlParameter("@ReviewID", SqlDbType.Int, 4);
			parameterReviewID.Value = reviewID;
			myCommand.Parameters.Add(parameterReviewID);

			// Execute the command
			myConnection.Open();
			SqlDataReader result = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

			// Return the datareader result
			return result;
		}
    }
}

